/*-- 标准递归语法都差不多 --*/
/*-- 前缀关键字有所不同: MySQL8,PostgreSQL,SQLite的是WITH RECURSIVE; DB2,SqlServer的是WITH --*/
/*-- 实测发现DB2与MySQL相比有一些差异: --*/
/*--   不支持_开头的字段名, 于是将_temp_parent_改为temp_parent --*/
/*--   不支持field AS alias, 于是去掉AS改成field alias --*/
/*--   WITH AS中不支持INNER JOIN, 于是改成 A, B WHERE A.PARENT=B.temp_parent --*/

-- import com.gitee.qdbp.tools.utils.ConvertTools

-- <<recursiveListChildrenQuery:normal>> 递归查询所有子节点, 标准递归语法
-- <supports>mysql.8,mariadb.10.2.2,postgresql,db2,h2,sqlserver,sqlite.3.8.3</supports>
<config key="recursive.keyword"/> recursive_temp_table(temp_parent) AS (
    SELECT ${codeColumn} temp_parent FROM ${tableName} 
    	WHERE <sql:in column="${codeColumn}" value="${startCodes}" />
		<append prefix="AND">#{filterWhere}</append>
    UNION ALL
    SELECT ${codeColumn} FROM ${tableName} A, recursive_temp_table B
    	WHERE A.${parentColumn}=B.temp_parent
		<append prefix="AND">#{filterWhere}</append>
)
SELECT ${selectColumns}
	FROM ${tableName} 
WHERE ${codeColumn} IN ( SELECT temp_parent FROM recursive_temp_table )
	<append prefix="AND">#{searchWhere}</append>
<append prefix="ORDER BY">#{orderBy}</append>


-- <<recursiveListChildrenQuery:mysql>> 递归查询所有子节点, mysql专用递归语法
/*-- 可能需要调整GROUP_CONCAT的长度: group_concat_max_len = 40960 --*/
SELECT DISTINCT ${selectColumns} FROM (
	SELECT T.ID__LIST FROM (
		SELECT  @IDS AS ID__LIST, (
			SELECT @IDS := GROUP_CONCAT( ${codeColumn} ) 
			FROM ${tableName} 
			WHERE FIND_IN_SET( ${parentColumn}, @IDS ) 
				<append prefix="AND">#{filterWhere}</append>
		) AS PID 
		FROM ${tableName}, ( SELECT @IDS := #{@ConvertTools.joinToString(startCodes)} ) T
	) T 
	WHERE T.ID__LIST IS NOT NULL
) T1, ${tableName} T2 
WHERE FIND_IN_SET( T2.${codeColumn}, T1.ID__LIST )
	<append prefix="AND">#{searchWhere}</append>
<append prefix="ORDER BY">#{orderBy}</append>


-- <<recursiveListChildrenQuery:oracle>> 递归查询所有子节点, oracle专用递归语法
SELECT DISTINCT ${selectColumns} FROM (
	SELECT * FROM ${tableName}
	START WITH <sql:in column="${codeColumn}" value="${startCodes}" />
		CONNECT BY PRIOR ${codeColumn}=${parentColumn}
		<append prefix="AND">#{filterWhere}</append>
        <append prefix="ORDER BY">#{orderBy}</append>
)
<append prefix="WHERE">#{searchWhere}</append>


-- <<recursiveListChildrenQuery>> 递归查询所有子节点, 使用存储过程实现的递归查询
/*-- MySQL存储过程有临时表, 可能需要设置: enforce_gtid_consistency = off --*/
CALL RECURSIVE_LIST_CHILDREN_QUERY (
    "${tableName}",
    "${startCodes}",
    "${codeColumn}",
    "${parentColumn}",
    "${selectColumns}",
    "${filterWhere}",
    "${searchWhere}",
    "${orderBy}"
);


-- <<recursiveListParentsQuery:normal>> 递归查询所有父节点, 标准递归语法
-- <supports>mysql.8,mariadb.10.2.2,postgresql,db2,h2,sqlserver,sqlite.3.8.3</supports>
<config key="recursive.keyword"/> recursive_temp_table(temp_parent) AS (
    SELECT ${codeColumn} temp_parent FROM ${tableName} 
    	WHERE <sql:in column="${codeColumn}" value="${startCodes}" />
		<append prefix="AND">#{filterWhere}</append>
    UNION ALL
    SELECT ${parentColumn} FROM ${tableName} A, recursive_temp_table B
    	WHERE B.temp_parent=A.${codeColumn}
		<append prefix="AND">#{filterWhere}</append>
)
SELECT ${selectColumns}
	FROM ${tableName} 
WHERE ${codeColumn} IN ( SELECT temp_parent FROM recursive_temp_table )
	<append prefix="AND">#{searchWhere}</append>
<append prefix="ORDER BY">#{orderBy}</append>


-- <<recursiveListParentsQuery:mysql>> 递归查询所有父节点, mysql专用递归语法
/*-- 可能需要调整GROUP_CONCAT的长度: group_concat_max_len = 40960 --*/
SELECT DISTINCT ${selectColumns} FROM (
	SELECT T.ID__LIST FROM (
		SELECT @IDS AS ID__LIST, (
			SELECT @IDS := GROUP_CONCAT(${parentColumn})
			FROM ${tableName} 
			WHERE FIND_IN_SET( ${codeColumn}, @IDS )
				<append prefix="AND">#{filterWhere}</append>
		) AS PID 
		FROM ${tableName}, ( SELECT @IDS := #{@ConvertTools.joinToString(startCodes)} ) T 
	) T
	WHERE T.ID__LIST IS NOT NULL
) T1, ${tableName} T2 
WHERE FIND_IN_SET( T2.${codeColumn}, T1.ID__LIST )
	<append prefix="AND">#{searchWhere}</append>
<append prefix="ORDER BY">#{orderBy}</append>


-- <<recursiveListParentsQuery:oracle>> 递归查询所有父节点, oracle专用递归语法
SELECT DISTINCT ${selectColumns} FROM (
	SELECT * FROM ${tableName}
	START WITH <sql:in column="${codeColumn}" value="${startCodes}" />
		CONNECT BY PRIOR ${parentColumn}=${codeColumn}
		<append prefix="AND">#{filterWhere}</append>
        <append prefix="ORDER BY">#{orderBy}</append>
)
<append prefix="WHERE">#{searchWhere}</append>


-- <<recursiveListParentsQuery>> 递归查询所有父节点, 使用存储过程实现的递归查询
/*-- MySQL存储过程有临时表, 可能需要设置: enforce_gtid_consistency = off --*/
CALL RECURSIVE_LIST_PARENTS_QUERY (
    "${tableName}",
    "${startCodes}",
    "${codeColumn}",
    "${parentColumn}",
    "${selectColumns}",
    "${filterWhere}",
    "${searchWhere}",
    "${orderBy}"
);
